package com.td.framework.webmanager.bmxx.dao.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.td.framework.common.BaseDao;
import com.td.framework.utils.DateUtils;
import com.td.framework.webmanager.bmxx.dao.BmxxDao;
import com.td.framework.webmanager.domain.AppsysBmxx;

/**
 * 
 * Title:部门信息处理数据层实现类 Description:处理部门信息 Copyright: Copyright (c) 2008 Company
 * 北京水软科技有限公司
 * 
 * @author FLT006
 * @version 1.0
 */
public class BmxxDaoImpl extends BaseDao implements BmxxDao {
	private static final Log log = LogFactory.getLog(BmxxDaoImpl.class);
	@SuppressWarnings("unchecked")
	private List list = null;// 部门信息列表;
	private String sql = "";// sql语句;
	private boolean delStatus;// 删除记录标志 true:彻底删除；false:象征性删除

	/**
	 * @toDo:保存部门信息
	 * @param bmxx
	 * @return boolean
	 */
	public boolean addBmxx(AppsysBmxx bmxx) {
		// TODO Auto-generated method stub
		boolean flag = false;
		try {
			if (bmxx != null) {
				bmxx.setBmbh(getNextBmbh(bmxx.getSjbmbh()));
				bmxx.setBmgdbh(getNextBmGdBh(bmxx.getBmbh()));// 设置部门固定编号
				bmxx.setCcs(new Long(bmxx.getSjbmbh().length() / 2 + 1));
				if (bmxx.getCjr() == null) {
					bmxx.setCjr("admin");
				}
				bmxx.setCjrq(DateUtils.currentDate());
				bmxx.setScbz(new Long(0));
				this.getHibernateTemplate().save(bmxx);
				flag = true;
			}
		} catch (RuntimeException re) {
			flag = false;
			log.error("Exception： BmxxDaoImpl+addBmxx", re);
			throw re;
		}
		return flag;
	}

	/**
	 * @toDo:产生部门编号;
	 * @param sjbmbh
	 * @return 部门编号;
	 */
	@SuppressWarnings("unchecked")
	public String getNextBmbh(String sjbmbh) {
		String bmbh = "";
		if (sjbmbh.equals("-1")) {
			bmbh = "01";
		} else {
			sql = " select t.bmbh from AppsysBmxx t " +
			" where length(t.bmbh)=length('"+sjbmbh+"')+2  and t.bmbh like '"+ sjbmbh + "%' order by t.bmbh";
			list = this.getHibernateTemplate().find(sql);
			if (list == null || list.size() == 0) {
				bmbh = sjbmbh + "01";
			} else {
				String temp = (String) list.get(list.size() - 1);
				bmbh = temp.substring(temp.length() - 3);
				int max = 0;// 最大值;
				int num = 0;// 临时存放数据;
				for (Iterator iterator = list.iterator(); iterator.hasNext();) {
					bmbh = iterator.next() + "";
					num = Integer.parseInt(bmbh.substring(bmbh.length() - 2));
					if (num > max) {
						max = num;
					}
				}
				max = max + 1;
				if (max < 10) {
					bmbh = sjbmbh + "0" + max;
				} else {
					bmbh = sjbmbh + max;
				}

			}
		}
		return bmbh;
	}

	/**
	 * @toDo:生成部门固定编号;
	 * @param bmbh
	 * @return 部门固定编号;
	 */
	public String getNextBmGdBh(String bmbh) {
		StringBuffer zero = new StringBuffer("");
		// 根据当前部门编号,生成固定编号;不足的补"0"
		for (int i = 0; i < 10 - bmbh.length(); i++) {
			zero.append("0");
		}
		return bmbh + zero.toString();
	}

	/**
	 * @toDo: 删除部门信息;
	 * @param bmbhs
	 * @return boolean;
	 */
	public boolean deleteBmxx(String[] bmbhs) {		
		// TODO Auto-generated method stub
		boolean flag = false;
		Connection con = null;
		try {
			con = getSession().connection();
			con.setAutoCommit(false);
			Statement st = con.createStatement();
			for (int i = 0; i < bmbhs.length; i++) {
				if (delStatus) {
					// 删除对应的权限关系
					sql = "delete from appsys_qxgx where qxbh in "
							+ " ( select qxbh from appsys_yhzqx where flag=1 "
							+ "and yhzbh in (select t.yhbh from appsys_dlyh t "
							+ "where t.rybh in (select t.rybh from appsys_rybmgx t "
							+ "where t.bmbh like '" + bmbhs[i].trim()
							+ "%') ))";
					st.addBatch(sql);
					// 删除对应的页面功能
					sql = "delete from appsys_qxgns where qxbh in "
							+ " ( select t.qxbh from appsys_yhzqx t where t.flag=1 "
							+ "and t.yhzbh in (select t.yhbh from appsys_dlyh t "
							+ "where t.rybh in (select t.rybh from appsys_rybmgx t "
							+ "where t.bmbh like '" + bmbhs[i].trim()
							+ "%') ))";
					st.addBatch(sql);
					// 删除对应的权限编号
					sql = "delete from appsys_qxxx where qxbh in "
							+ " ( select t.qxbh from appsys_yhzqx t where t.flag=1 "
							+ "and t.yhzbh in (select t.yhbh from appsys_dlyh t "
							+ "where t.rybh in (select t.rybh from appsys_rybmgx t "
							+ "where t.bmbh like '" + bmbhs[i].trim()
							+ "%') ))";
					st.addBatch(sql);
					// 删除对应的用户、权限关系
					sql = " delete from appsys_yhzqx where flag=1 "
							+ "and t.yhzbh in (select t.yhbh from appsys_dlyh t "
							+ "where t.rybh in (select t.rybh from appsys_rybmgx t "
							+ "where t.bmbh like '" + bmbhs[i].trim() + "%') )";
					st.addBatch(sql);
					// 删除对应的用户、用户组关系
					sql = "delete from appsys_yhyhzgx where yhbh in "
							+ "(select t.yhbh from appsys_dlyh t "
							+ "where t.rybh in (select t.rybh from appsys_rybmgx t "
							+ "where t.bmbh like '" + bmbhs[i].trim() + "%') )";
					st.addBatch(sql);
					// 删除对应的用户
					sql = "delete from appsys_dlyh "
							+ "where rybh in (select t.rybh from appsys_rybmgx t "
							+ "where t.bmbh like '" + bmbhs[i].trim() + "%')";
					st.addBatch(sql);
					// 删除人员
					sql = "delete from appsys_ryjbxx "
							+ "where rybh in (select t.rybh from appsys_rybmgx t "
							+ "where t.bmbh like '" + bmbhs[i].trim() + "%')";
					st.addBatch(sql);
					// 删除人员、部门关系
					sql = "delete from appsys_rybmgx  where bmbh like '"
							+ bmbhs[i].trim() + "%' ";
					st.addBatch(sql);
					// 删除部门
					sql = " delete from appsys_bmxx where bmbh like '"
							+ bmbhs[i].trim() + "%' ";
					st.addBatch(sql);
				} else {
					// 删除对应的权限关系
					sql = "update appsys_qxgx t set t.scbz=1 where t.qxbh in "
							+ " ( select t.qxbh from appsys_yhzqx t where t.flag=1 "
							+ "and t.yhzbh in (select t.yhbh from appsys_dlyh t "
							+ "where t.rybh in (select t.rybh from appsys_rybmgx t "
							+ "where t.bmbh like '" + bmbhs[i].trim()
							+ "%') ))";
					st.addBatch(sql);
					// 删除对应的页面功能
					sql = "update appsys_qxgns t set t.scbz=1 where t.qxbh in "
							+ " ( select t.qxbh from appsys_yhzqx t where t.flag=1 "
							+ "and t.yhzbh in (select t.yhbh from appsys_dlyh t "
							+ "where t.rybh in (select t.rybh from appsys_rybmgx t "
							+ "where t.bmbh like '" + bmbhs[i].trim()
							+ "%') ))";
					st.addBatch(sql);
					// 删除对应的权限编号
					sql = "update appsys_qxxx t set t.scbz=1 where t.qxbh in "
							+ " ( select t.qxbh from appsys_yhzqx t where t.flag=1 "
							+ "and t.yhzbh in (select t.yhbh from appsys_dlyh t "
							+ "where t.rybh in (select t.rybh from appsys_rybmgx t "
							+ "where t.bmbh like '" + bmbhs[i].trim()
							+ "%') ))";
					st.addBatch(sql);
					// 删除对应的用户、权限关系
					sql = " update appsys_yhzqx t set t.scbz=1 where t.flag=1 "
							+ "and t.yhzbh in (select t.yhbh from appsys_dlyh t "
							+ "where t.rybh in (select t.rybh from appsys_rybmgx t "
							+ "where t.bmbh like '" + bmbhs[i].trim() + "%') )";
					st.addBatch(sql);
					// 删除对应的用户、用户组关系
					sql = "update appsys_yhyhzgx t set t.scbz=1 where t.yhbh in "
							+ "(select t.yhbh from appsys_dlyh t "
							+ "where t.rybh in (select t.rybh from appsys_rybmgx t "
							+ "where t.bmbh like '" + bmbhs[i].trim() + "%') )";
					st.addBatch(sql);
					// 删除对应的用户
					sql = "update appsys_dlyh a set a.scbz=1 "
							+ "where a.rybh in (select t.rybh from appsys_rybmgx t "
							+ "where t.bmbh like '" + bmbhs[i].trim() + "%')";
					st.addBatch(sql);
					// 删除人员
					sql = "update appsys_ryjbxx a  set a.scbz=1 "
							+ "where a.rybh in (select t.rybh from appsys_rybmgx t "
							+ "where t.bmbh like '" + bmbhs[i].trim() + "%')";
					st.addBatch(sql);
					// 删除人员、部门关系
					sql = "update appsys_rybmgx b  set b.scbz=1 "
							+ "where b.bmbh like '" + bmbhs[i].trim() + "%' ";
					st.addBatch(sql);
					// 删除部门
					sql = " update appsys_bmxx t set t.scbz=1 where t.bmbh like '"
							+ bmbhs[i].trim() + "%' ";
					st.addBatch(sql);
				}
			}
			st.executeBatch();
			con.commit();
			//con.setAutoCommit(true);
			flag = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			flag = false;
			log.error("删除部门时错误Exception： BmxxDaoImpl+deleteBmxx", e);
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					flag = false;
					log.error("数据库关闭错误Exception： BmxxDaoImpl+deleteBmxx", e);
				}
			}
		}

		return flag;
	}

	/**
	 * @toDo:得到总记录数;
	 * @return rows;
	 */
	@SuppressWarnings("unchecked")
	public int getRows() {
		int rows = 0;
		Session session = getSession();
		List list = session.createSQLQuery(sql).list();
		rows = list.size();
		return rows;
	}

	/**
	 * @toDo 通过上级部门编号查询部门信息;
	 * @param sjbmbh
	 * @param defaultBmbh
	 * @param pageSize,startRow
	 * @return 部门集合;
	 */
	@SuppressWarnings("unchecked")
	public List<AppsysBmxx> findBySjbmbh(String sjbmbh,String defaultBmbh, int pageSize,
			int startRow) {
		if (sjbmbh.trim().equals("null")) {
			list = null;
		} else {
			list = getBmxxListBySql(sql,defaultBmbh,pageSize,startRow);
		}
		return list;
	}

	/**
	 * @toDo 通过上级部门编号查询部门信息;
	 * @param sjbmbh
	 * @param pageSize,startRow
	 * @return 部门集合;
	 */
	@SuppressWarnings("unchecked")
	public List<AppsysBmxx> findBySjbmbh(String sjbmbh) {
		if (sjbmbh.trim().equals("") || sjbmbh.trim().equals("null")) {
			list = null;
		} else {
			sql = "from AppsysBmxx t "
					+ " where t.scbz=0 and t.sjbmbh = '" + sjbmbh + "' "
					+ " 	order by t.pxh ";
			list = this.getHibernateTemplate().find(sql);
		}
		return list;
	}

	/**
	 * @toDo 部门排序;
	 * @param appsysBmxxs
	 * @return
	 */
	public boolean orderBmxx(String[] bmId, String[] pxh) {
		boolean flag = false;
		Connection con = null;
		try {
			con = getSession().connection();
			con.setAutoCommit(false);
			Statement st = con.createStatement();
			for (int i = 0; i < bmId.length; i++) {
				sql = "update appsys_bmxx t set t.pxh=" + pxh[i]
						+ " where t.id = " + bmId[i];
				st.addBatch(sql);
			}
			st.executeBatch();
			con.commit();
			con.setAutoCommit(true);
			flag = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			flag = false;
			log.error("Exception： BmxxDaoImpl+orderBmxx", e);
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					flag = false;
					log.error("Exception： BmxxDaoImpl+orderBmxx", e);
				}
			}
		}
		return flag;

	}

	/**
	 * @toDo: 通过部门编号得到部门信息;
	 * @param bmbh
	 * @return 部门信息对象;
	 */
	public AppsysBmxx getBmxx(String bmbh) {
		// TODO Auto-generated method stub
		sql = "from AppsysBmxx t "
				+ " where t.bmbh='" + bmbh.trim() +"' ";
		list = this.getHibernateTemplate().find(sql);
		AppsysBmxx bmxx = null;
		if (list.size() > 0) {
			bmxx = (AppsysBmxx) list.get(0);
		}
		return bmxx;
	}

	/**
	 * @toDo: 通过部门固定编号得到部门信息;
	 * @param bmbh
	 * @return 部门信息对象;
	 */
	public AppsysBmxx getBmxxBygdbh(String bmgdbh) {
		// TODO Auto-generated method stub
		sql = "from AppsysBmxx t "
				+ " where t.bmgdbh='" + bmgdbh + "' ";
		list = this.getHibernateTemplate().find(sql);
		AppsysBmxx bmxx = null;
		if (list.size() > 0) {
			bmxx = (AppsysBmxx) list.get(0);
		}
		return bmxx;
	}

	/**
	 * @toDo 得到生成组织结构树的部门集合;
	 * @return 部门集合;
	 */
	@SuppressWarnings("unchecked")
	public List getTreeList() {
		// TODO Auto-generated method stub
		sql = "from AppsysBmxx t "
				+ " where t.scbz=0 order by t.ccs,t.pxh,t.bmbh";
		list = this.getHibernateTemplate().find(sql);
		return list;
	}

	/**
	 * @toDo:更新部门信息;
	 * @param bmxx
	 * @return boolean;
	 */
	public boolean updateBmxx(AppsysBmxx bmxx) {
		// TODO Auto-generated method stub
		boolean flag = false;
		try {
			if (bmxx != null) {
				bmxx.setCcs(new Long(bmxx.getSjbmbh().length() / 2 + 1));
				this.getHibernateTemplate().update(bmxx);
				flag = true;
			}
		} catch (RuntimeException re) {
			flag = false;
			log.error("Exception： BmxxDaoImpl+updateBmxx", re);
			throw re;
		}
		return flag;
	}

	/**
	 * toDo:通过用户/用户组编号结合部门权限查询部门信息
	 * 
	 * @param yhbh
	 *            用户编号
	 * @param defaultBmbh 默认部门编号
	 * @param yhlb
	 *            用户类别 ,0 为用户组,1 为用户
	 * @param xtbm
	 *            系统编码
	 * @param flag
	 *            0为部门分配时用, 1为生成与权限结合的部门树
	 * @return list;
	 */
	@SuppressWarnings("unchecked")
	public List<AppsysBmxx> findBmxxByYhbh(String yhbh,String defaultBmbh, int yhlb, String xtbm,
			int flag) {
		List<AppsysBmxx> list = null;
		sql = "select distinct nvl(a.flag,-1) as id ,t.bmbh,t.bmmc,t.sjbmbh,t.bmms,"
				+ "t.ccs,t.scbz,t.pxh,t.cjr,t.cjrq,t.bmgdbh,t.pyzt from appsys_bmxx t "
				+ "left join ( select (1) as flag ,a.bmbm from appsys_bmqx a "
				+ "where a.scbz=0 and a.yhbh='"
				+ yhbh
				+ "' and a.yhlb="
				+ yhlb
				+ " and a.xtbm='"
				+ xtbm
				+ "') a "
				+ "on t.bmbh = a.bmbm where t.scbz=0  order by t.ccs,t.pxh,t.bmbh";
		if (flag == 1) {
			//超级管理员
			if (yhbh.equals("-1")) 
			{
				sql = "select  distinct (1) as id ,t.bmbh,t.bmmc,t.sjbmbh,t.bmms," +
				"t.ccs,t.scbz,t.pxh,t.cjr,t.cjrq,t.bmgdbh,t.pyzt from " +
				" appsys_bmxx t where t.scbz=0  order by t.ccs,t.pxh,t.bmbh ";
			}
			else 
			{
				sql = "select  distinct (1) as id ,t.bmbh,t.bmmc,t.sjbmbh,t.bmms," +
				"t.ccs,t.scbz,t.pxh,t.cjr,t.cjrq,t.bmgdbh,t.pyzt from " +
				" appsys_bmxx t where t.scbz=0  order by t.ccs,t.pxh,t.bmbh ";
			}
		}
		list = getBmxxListBySql(sql,defaultBmbh,-1,-1);
		return list;
	}
	/**
	 * toDo:通过sql语句查询部门信息列表
	 * @param sql
	 * @param defaultBmbh
	 * @return list;
	 */
	public List<AppsysBmxx> getBmxxListBySql(String sql,String defaultBmbh,int pageSize,int startRow)
	{
		List<AppsysBmxx> list = null;
		Session session = this.getSession();
		SQLQuery query = session.createSQLQuery(sql);
		if(pageSize!=-1)
		{
			query.setFirstResult(startRow);
			query.setMaxResults(pageSize);
		}
		List<Object[]> tempList = query.list();
		if (tempList != null && tempList.size() > 0) {
			list = new ArrayList();
			AppsysBmxx bmxx = new AppsysBmxx();
			for (Iterator iterator = tempList.iterator(); iterator.hasNext();) {
				Object[] objects = (Object[]) iterator.next();
				bmxx = new AppsysBmxx();
				bmxx.setId(Long.parseLong(objects[0] + ""));
				if((objects[1] + "").equals(defaultBmbh))
				{
					bmxx.setId(Long.parseLong("1"));
				}
				bmxx.setBmbh(objects[1] + "");
				bmxx.setBmmc(objects[2] + "");
				bmxx.setSjbmbh(objects[3] + "");
				bmxx.setBmms(objects[4] + "");
				bmxx.setCcs(Long.parseLong(objects[5] + ""));
				bmxx.setScbz(Long.parseLong(objects[6] + ""));
				bmxx.setPxh(Long.parseLong(objects[7] + ""));
				bmxx.setCjr(objects[8] + "");
				try {
					bmxx.setCjrq(DateUtils.parse(objects[9].toString()));
				} catch (ParseException e) {
					log.error(e.toString());
					e.printStackTrace();
				}
				bmxx.setBmgdbh(objects[10] + "");
				bmxx.setPyzt(objects[11] + "");
				list.add(bmxx);
			}
		}
		return list;
	}
	/**
	 * @toDo:get删除记录标志;
	 * @return the delStatus
	 */
	public boolean isDelStatus() {
		return delStatus;
	}

	/**
	 * @toDo:set删除记录标志;
	 * @param delStatus
	 *            the delStatus to set
	 */
	public void setDelStatus(boolean delStatus) {
		this.delStatus = delStatus;
	}

	/**
	 * @toDo:get查询语句;
	 * @return the sql
	 */
	public String getSql() {
		return sql;
	}

	/**
	 * @toDo:set查询语句;
	 * @param sql the sql to set
	 */
	public void setSql(String sjbmbh, String bmmc,String yhbh,String defaultBmbh,String xtbm)  {
		String conditon = "";
		conditon = " where t.scbz=0 and t.sjbmbh = '" + sjbmbh + "' ";
		if (sjbmbh.equals("-1") ||sjbmbh.equals("")) {
			conditon = " where t.scbz=0 and t.sjbmbh != '-1'";
		}
		if (!bmmc.trim().equals("") && !bmmc.trim().equals("null")) {
			conditon += " and t.bmmc like '%" + bmmc.trim() + "%' ";
		}
		//超级管理员
		if (yhbh.equals("-1")) 
		{
			sql = "select t.id ,t.bmbh,t.bmmc,t.sjbmbh,t.bmms," +
			"t.ccs,t.scbz,t.pxh,t.cjr,t.cjrq,t.bmgdbh,t.pyzt from " +
			" appsys_bmxx t "+conditon+"  order by t.ccs,t.pxh,t.bmbh ";
		}
		else 
		{
			sql = "select t.id ,t.bmbh,t.bmmc,t.sjbmbh,t.bmms," +
			"t.ccs,t.scbz,t.pxh,t.cjr,t.cjrq,t.bmgdbh,t.pyzt from " +
			" appsys_bmxx t "+conditon+"  order by t.ccs,t.pxh,t.bmbh ";
		}
	}
	
	/**
	 * 查询用用户名称是否被占用
	 */
	public boolean checkBmmc(String bmmc){
		String hql ="from AppsysBmxx t where t.bmmc='"+bmmc+"'";
		int result = this.getHibernateTemplate().find(hql).size();
		if(result>0){
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * 查询该部门下面是否还有人员
	 * @param bmbh 部门编号
	 */
	public  boolean checkHasPeople(String bmbh){
		String hql = "from AppsysRybmgx t where t .bmbh like '"+bmbh+"%' and t.scbz = 0";
		 int size = this.getHibernateTemplate().find(hql).size();
		 if(size>0){
				return true;
		 }else{
				return false;
		 }
	}
}
